package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.repository.RoadRepository;
import com.css.fxfzypg.modules.baseData.entity.Road;
import com.css.fxfzypg.modules.baseData.queryParams.RoadQueryParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class RoadRepositoryImpl implements RoadRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Road updateRoadById(Road road) {
        return null;
    }

    @Override
    public void deleteRoadsById(List<Integer> ids, Timestamp timestamp) {
        String sql = "update sys_road set road_status = '" + YNEnum.N .toString()+ "' ,road_updatetime = '"+timestamp+"'" + " where road_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public Road saveRoad(Road road) {
//        String sql = "insert into sys_road(road_name,road_type,road_status) values(:roadName,:roadType,:roadStatus)";
//        SqlParameterSource source = new BeanPropertySqlParameterSource(road);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
//        return road;
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "";
                sql = "insert into sys_road(road_name,road_type,road_status) values(?,?,?)";
                PreparedStatement ps = connection.prepareStatement(sql, new String [] {"road_id"});
                ps.setString(1, road.getRoadName());
                ps.setString(2, road.getRoadType());
                ps.setString(3, road.getRoadStatus());
                return ps;
            }
        }, keyHolder);
        int i = keyHolder.getKey().intValue();
        road.setRoadId(i);
        return road;




    }


    @Override
    public Page<Road> getAllRoadParams(RoadQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  r.* from sys_road r ");

        StringBuilder orderSql = new StringBuilder(" order by r.road_id");

        StringBuilder whereSql = new StringBuilder(" where 1=1 ");

        if (!PlatformObjectUtils.isEmpty(queryParams.getRoadName()) ){
            whereSql.append(" and r.road_name like '%" + queryParams.getRoadName() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getRoadType()) ){
            whereSql.append(" and r.road_type like '%" + queryParams.getRoadType() + "%'");
        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<Road> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(Road.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<Road> roads = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return roads;
    }

    @Override
    public Road getRoadById(Integer id) {
        String sql = "select * from sys_road where road_id = '"+id+"'";
        Road road = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Road.class));
        return road;
    }


    @Override
    public List<Road> getAll() {
        return jdbcTemplate.query("select * from sys_road",new BeanPropertyRowMapper<>(Road.class));
    }

    @Override
    public void importExcel(List<Road> roads) {
        jdbcTemplate.batchUpdate("insert into sys_road (road_name,road_type,road_status)values(?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return roads.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, roads.get(i).getRoadName());
                ps.setString(2,roads.get(i).getRoadType());
                ps.setString(3,roads.get(i).getRoadStatus());

            }
        });
    }

    @Override
    public Integer countByCounty(String name, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_road where county = '" + county + "' and city = '" + city + "' and province = '" + name + "'   or (province ='"+name+"' and city ='' and county ='')    and road_status='1'  ", Integer.class);
    }

    @Override
    public Integer countByCity(String name, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_road where city = '" + city + "' and province = '" + name + "'   or (province ='"+name+"' and city ='' and county ='')   and road_status='1'   ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_road where province = '" + name + "'  and road_status='1'  and city = ''   ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(t1.*) from sys_road t1 LEFT JOIN qh_map_files t2 on t1.fileuuid = t2.uuid where t1.province = ? and (t2.update_time LIKE ? or t2.create_time LIKE ?) and t1.road_status='1'", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");
    }

    @Override
    public List<Road> getRoadByDistrict(String province, String city, String county) {
        String sql = "select *,ST_AsText(geom) geomText from sys_road where  province='"+province+"'  and road_status='1'";
        if(StringUtils.isNotBlank(city)){
            sql+=" and city='"+city+"' ";
        }
        if(StringUtils.isNotBlank(county)){
            sql+="  and county='"+county+"' ";
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Road.class));
    }

    @Override
    public boolean getRoadByGeom(String geomText, String taskRegion) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490))";
        Boolean aBoolean = true;
        try{
            aBoolean = jdbcTemplate.queryForObject(sql, Boolean.class, geomText, taskRegion);
        }catch (Exception e){
            e.printStackTrace();
        }
        return aBoolean;
    }

    @Override
    public boolean getRoadByTaskId(String geomText, String taskId) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "(select geom from ypg_preassess_task where id=? and del_flag='0'))";
        Boolean aBoolean = true;
        try{
            aBoolean = jdbcTemplate.queryForObject(sql, Boolean.class, geomText, taskId);
        }catch (Exception e){
            e.printStackTrace();
        }
        return aBoolean;
    }
}
